<?php
require_once 'Zend/Db/Table/Abstract.php';
class SubjectsModel extends Zend_Db_Table_Abstract
{
	protected $_name = 'subjects';
	protected $_id = 'id';
	
	/*
	 * Ham kiem tra mon hoc da ton tai hay chua.
	 */
	public function getIdsubjectsByCode($code){
		$db = Zend_Registry::get('connectDB');
		$mysql = $db -> select()
					->from('subjects')
					->where("code =?",$code);
		$list = $db->fetchAll($mysql);
		if(count($list)) return $list[0]["id"];
		else return 0;
	}

	
	/*
	 * Ham tra ve cac mon hoc ma giao vien day trong ki
	 */
	
	public function getSubjectOfTeacher($idTeacher){
		$date = date("Y-m-d H:i:s");
		$db = Zend_Registry::get('connectDB');
		$mysql = $db ->select()
						->from('teachers',array('name_teachers' => 'teachers.name','id_t'=>'teachers.id'))
						->join(array('sj' => 'subjects'),'sj.id_teachers  = teachers.id')
						->join(array('r' => 'room'),'sj.id_room  = r.id',array('name_r' => 'r.name'))
						->join(array('i' => 'institutes'),'teachers.id_institutes  = i.id',array('name_i' => 'i.name'))
						->join(array('s' => 'semester'),'s.id = sj.id_semester',array('name_s' => 's.name','id_s' => 's.id'))
						->where("teachers.id =?",$idTeacher)
						->where("s.time_start <=?",$date)
						->where("s.time_end >=?",$date);
		//	echo $mysql->__toString();exit();
		$result = $db->fetchAll($mysql);
		return  $result;
	}

	public function getSubjectOfTeacherBySemester($idTeacher,$semester){
		$date = date("Y-m-d H:i:s");
		$db = Zend_Registry::get('connectDB');
		$mysql = $db ->select()
						->from('teachers',array('name_teachers' => 'teachers.name','id_t'=>'teachers.id'))
						->join(array('sj' => 'subjects'),'sj.id_teachers  = teachers.id')
						->join(array('r' => 'room'),'sj.id_room  = r.id',array('name_r' => 'r.name'))
						->join(array('i' => 'institutes'),'teachers.id_institutes  = i.id',array('name_i' => 'i.name'))
						->join(array('s' => 'semester'),'s.id = sj.id_semester',array('name_s' => 's.name'))
						->where("teachers.id =?",$idTeacher)
						->where("s.id =?",$semester);
		//	echo $mysql->__toString();exit();
		$result = $db->fetchAll($mysql);
		return  $result;
	}
    
    
		/*
	 * Lay danh sach cac sinh vien
	 */
	public static function getAll(){
		$db = Zend_Registry::get('connectDB');
		$mySql = $db->select()
					->from('subjects');
		$list = $db->fetchAll($mySql);
		return $list;
	}
	
	/*
	 * Lay danh sach sinh vien co gioi han so luog
	 */
	
	public function selectLimit($count,$limit){
		$db = Zend_Registry::get('connectDB');
		$mysql = $db -> select()
					->from('subjects')
					->limit($count,$limit)
					->order("id DESC");
		$list = $db->fetchAll($mysql);
		return  $list;
	}
	
	/*
     * Ham lay thong tin sinh vien qua id
     */  
    public function getDbbyID($id){
        $db= Zend_Registry::get('connectDB');
        $mysql= $db->select()
            ->from($this->_name)
            ->where('id=?',$id);
        $list= $db->fetchAll($mysql);
        return $list;
    }
    
    /*
	 * Tìm kiếm .
	 */
 	public function search($code_search,$c_search){
 		$db = Zend_Registry::get('connectDB');
 		$mysql = $db ->select('id')
			 		 ->from('subjects');
 					
		if($c_search){
		  $mysql->where("subjects.id_room =?",$c_search);
		}
        $mysql->where("subjects.code LIKE ?",'%'.$code_search.'%');
		
        
	//	echo $mysql->__toString();exit();
		$list = $db->fetchAll($mysql);
		return $list;
 	}
  
	/*
	 * Ham lay cac bai tap cua mon hoc
	 */
    
 	function getExercisesOfSubject($id){
 		$db = Zend_Registry::get('connectDB');
 		$mysql = $db ->select()
			 		->from('subjects')
			 		->join(array('e' => 'exercises'),'e.id_subjects  = subjects.id')
			 		->where("subjects.id =?",$id);
 		//	echo $mysql->__toString();exit();
 		$result = $db->fetchAll($mysql);
 		return  $result;
 	}
 	
 	/*
 	 * Ham cat chu
 	 */
 	
 	// cat chu
 	
 	function catchu($value,$length)
 	{
 		if($value!=''){
 			if(is_array($value)) list($string, $match_to) = $value;
 			else { $string = $value; $match_to = $value{0};
 			}
 	
 			$match_start = stristr($string, $match_to);
 			$match_compute = strlen($string) - strlen($match_start);
 	
 			if (strlen($string) > $length)
 			{
 				if ($match_compute < ($length - strlen($match_to)))
 				{
 					$pre_string = substr($string, 0, $length);
 					$pos_end = strrpos($pre_string, " ");
 					if($pos_end === false) $string = $pre_string."...";
 					else $string = substr($pre_string, 0, $pos_end)."...";
 				}
 				else if ($match_compute > (strlen($string) - ($length - strlen($match_to))))
 				{
 					$pre_string = substr($string, (strlen($string) - ($length - strlen($match_to))));
 					$pos_start = strpos($pre_string, " ");
 					$string = "...".substr($pre_string, $pos_start);
 					if($pos_start === false) $string = "...".$pre_string;
 					else $string = "...".substr($pre_string, $pos_start);
 				}
 				else
 				{
 					$pre_string = substr($string, ($match_compute - round(($length / 3))), $length);
 					$pos_start = strpos($pre_string, " "); $pos_end = strrpos($pre_string, " ");
 					$string = "...".substr($pre_string, $pos_start, $pos_end)."...";
 					if($pos_start === false && $pos_end === false) $string = "...".$pre_string."...";
 					else $string = "...".substr($pre_string, $pos_start, $pos_end)."...";
 				}
 	
 				$match_start = stristr($string, $match_to);
 				$match_compute = strlen($string) - strlen($match_start);
 			}
 	
 			return $string;
 		}else{
 			return $string ='';
 		}
 	}
 	
 	/*
 	 * Ham tra ve ten mon hoc.
 	*/
 	public function getNameSubject($id){
 		$db = Zend_Registry::get('connectDB');
 		$mysql = $db -> select()
			 		->from('subjects')
			 		->where("id =?",$id);
 		$list = $db->fetchAll($mysql);
 		return $list[0]["name"];
 	}

 	/*
	* Hàm trả về mã môn học
 	*/
	public function getCodeSubject($id){
 		$db = Zend_Registry::get('connectDB');
 		$mysql = $db -> select()
			 		->from('subjects')
			 		->where("id =?",$id);
 		$list = $db->fetchAll($mysql);
 		return $list[0]["code"];
 	}
    
    /*
    Backcx
    */
      /* Hàm lấy các thông tin liên quan qua id môn học*/
		public function getDbRelativeById($id_sub){
 	
        $db = Zend_Registry::get('connectDB');
		$mysql = $db ->select()
					->from('subjects')
					->joinLeft('teachers','subjects.id_teachers=teachers.id',array('teachers.name as t_name'))
					->joinLeft('room','subjects.id_room=room.id',array('room.name as r_name'))
					->joinLeft('semester','subjects.id_semester=semester.id',array('semester.name as s_name'))
					->where("subjects.id =?",$id_sub);
				
	
		$result = $db->fetchAll($mysql);

		return  $result;
 	}
 	
}